package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.formAddReviewer;
import forms.formDecision;
import forms.formDeleteReviewer;
import forms.formReview;
import forms.formUpdateReviewer;

public class reviewerModel {

	private static String selectStr = "SELECT user_ref FROM user_roles WHERE role='reviewer'";
	private static String addReviewer = "INSERT INTO users (user_name,password,email) VALUES (?, md5(?), ?)";
	private static String addReviewerRole = "INSERT INTO user_roles (user_ref,user_name,role) VALUES (?,?,?)";
	private static String updateStr = "UPDATE users SET user_name = ?, password = ?, email = ?, WHERE user_name = ?";
	private static String deleteStr = "DELETE FROM users WHERE user_name = ?";
	private static String deleteStrRole = "DELETE FROM user_role WHERE user_id = ?";

	public static CachedRowSet getAllReviewer() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectStr);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allReviewer = pStmt.executeQuery();

			CachedRowSet crsReviewer = new CachedRowSetImpl();
			crsReviewer.populate(allReviewer);

			allReviewer.close();
			pStmt.close();
			conn.close();

			return crsReviewer;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void addReviewer(formAddReviewer reviewer) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			PreparedStatement pStmt = conn.prepareStatement(addReviewer);

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());
			pStmt.setString(3, "");
			pStmt.executeUpdate();

			pStmt = conn.prepareStatement(addReviewerRole);
			Statement stmt = conn.createStatement();
			// ResultSet cause DB exception
			ResultSet rs = stmt
					.executeQuery("SELECT user_id from users WHERE user_name ='"
							+ reviewer.getUsername() + "'");
			rs.next();
			pStmt.setInt(1, rs.getInt(1));
			pStmt.setString(2, reviewer.getUsername());
			pStmt.setString(3, "reviewer");
			pStmt.executeUpdate();
			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void updateReviewer(formUpdateReviewer reviewer)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(selectStr);
			rs.next();
			PreparedStatement pStmt = conn.prepareStatement(updateStr);

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());
			pStmt.setString(3, "");
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void deleteReviewer(formDeleteReviewer reviewer)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// DELETE students FROM the Students table.
			PreparedStatement pStmt = conn.prepareStatement(deleteStr);
			PreparedStatement pStmt_2 = conn.prepareStatement(deleteStrRole);

			pStmt.setString(1, reviewer.getUserName());
			pStmt.executeUpdate();

			pStmt.setString(1, reviewer.getUserName());
			pStmt.executeUpdate();

			// Commit transaction
			conn.commit();

			pStmt.close();
			pStmt_2.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	public static void addGradeResult(formReview reviewer)
	throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pStmt = conn.prepareStatement("INSERT INTO grade_result (student_id,grade,comment) VALUES (?,?,?)");
			pStmt.setInt(1, Integer.parseInt(reviewer.getId()));
			pStmt.setString(2, reviewer.getGrade());
			pStmt.setString(3, reviewer.getComment());
			pStmt.executeUpdate();
			conn.commit();
			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
}
